package contacts530.dao1.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import contacts530.dao1.IStudentsDao;
import contacts530.db.Dbconnection;
import contacts530.po.Room;
import contacts530.po.Students;

import contacts530.db.Dbconnection;

public class StudentsDaoImpl implements IStudentsDao {
	Dbconnection db = new Dbconnection();
	Connection conn = db.getConnection();
	int dbo;
	int qq;
	int email;
	int gander;
	int phone;
	String name;
	String room_no;	
	String sql = "insert into student values(?,?,?,?,?,?,?)";
	PreparedStatement ps = null;
	private List<Students> studentList;
	private Room room;

	/**
	 *新增通讯录
	 */
	@Override
	public void save(Students student) {
		int dbo=student.getDbo();
		String room_no=student.getRoom_no();
		int phone=student.getPhone();
		String name=student.getName();
		int gander=student.getGander();
		int email=student.getEmail();
		int qq=student.getQQ();// TODO Auto-generated method stub
		String sql = "insert into student values(?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps  = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, room_no);
			ps.setInt(3, dbo);
			ps.setInt(4, gander);
			ps.setInt(5, phone);
			ps.setInt(6, email );
			ps.setInt(7, qq);
			ps.executeUpdate();
			ps.executeUpdate();
		}catch(SQLException e) {
			
		}
			finally {
				Dbconnection.close(null, (ResultSet) ps);
		}
	}

	/**
	 * 修改图书
	 */
	
	@Override
	public void update(Students student) {
		String room_no=student.getRoom_no();
		int Fhone_id=student.getPhone();
		String name=student.getName();
		int birthday=student.getDbo();
		int address=student.getEmail();
		int qq=student.getQQ();// TODO Auto-generated method stub
		String sql = "update student set name=?，room_no=?，dbo=?，gander=?，phone=?,email=?,qq=? where name=?";
		PreparedStatement ps = null;
		try {
			ps  = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, room_no);
			ps.setInt(3, dbo);
			ps.setInt(4, gander);
			ps.setInt(5, phone);
			ps.setInt(6, email );
			ps.setInt(7, qq);
			ps.executeUpdate();
		}catch(SQLException e) {		
		}
			finally {
				Dbconnection.close(null, (ResultSet) ps);
		}
	}// TODO Auto-generated method stub
	
 // TODO Auto-generated method stub
	@Override
	public List<Students> findAll() {
		Connection conn = Dbconnection.getConnection();
		List<Students> studentList = new ArrayList<Students>();
		String sql = "SELECT s.student_id,s.student_name,s.Fhone_id";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()){
				String name = rs.getString(1);
				String room_no = rs.getString(2);
				int gander =rs.getInt(3);
				int dbo = rs.getInt(4);
				int phone=rs.getInt(5);
				int email=rs.getInt(6);
				int qq=rs.getInt(7);
				Students student = new Students(name,room_no,gander,dbo,phone,email,qq);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dbconnection.close(null, (ResultSet) ps);
		}
		return studentList;
	}	// TODO Auto-generated method stub

	@Override/**
	 *删除通讯录里的信息
	 *	 
	 **/
	public void delete(String name) {
		String sql = "delete from student where name=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dbconnection.close(null, (ResultSet) ps);
		}// TODO Auto-generated method stub
		
	}
	/**
	 *查询通讯录
	 */
	@Override
	public Students get(String name) {
		List<Students> studentList = new ArrayList<Students>();
		String sql = "select * from student where name=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while (rs.next()){			
				String room_no = rs.getString(1);
				int gander =rs.getInt(2);
				int dbo = rs.getInt(3);
				int phone=rs.getInt(4);
				int email=rs.getInt(5);
				int qq=rs.getInt(6);
				Students student = new Students(name,room_no,gander,dbo,phone,email,qq);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dbconnection.close(null, (ResultSet) ps);
		}
		return studentList.get(0);	//返回第一个人，集合 索引为0
	// TODO Auto-generated method stub
		
	}

	@Override
	public List<Room> findAllRoom() {
		// TODO Auto-generated method stub
		return null;
	}

	
	}

	
	



	


